﻿using Microsoft.Extensions.Logging;
using Microsoft.VisualBasic;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

using Volo.Abp.DependencyInjection;
using Volo.Abp.Validation;

using Wms.Manager.Common.Dtos;
using Wms.Manager.Common.ExtendAttribute;

namespace Wms.Manager.Common
{
    public class ImportExportService<T> : IImportExportService<T>, ITransientDependency where T : class, new()
    {
        private readonly ILogger<ImportExportService<T>> _logger;

        public ImportExportService(ILogger<ImportExportService<T>> logger)
        {
            _logger = logger;
        }

        public virtual async Task<byte[]> ExportToExcelAsync(IEnumerable<T> data, string fileName = null)
        {
            try
            {
                var workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet(fileName ?? typeof(T).Name);

                var properties = GetExportProperties();

                // 创建表头样式
                var headerStyle = workbook.CreateCellStyle();
                var headerFont = workbook.CreateFont();
                headerFont.IsBold = true;
                headerStyle.SetFont(headerFont);
                headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                headerStyle.FillPattern = FillPattern.SolidForeground;
                headerStyle.BorderBottom = BorderStyle.Thin;
                headerStyle.BorderTop = BorderStyle.Thin;
                headerStyle.BorderLeft = BorderStyle.Thin;
                headerStyle.BorderRight = BorderStyle.Thin;

                // 设置表头
                var headerRow = sheet.CreateRow(0);
                for (int i = 0; i < properties.Count; i++)
                {
                    var prop = properties[i];
                    var attr = prop.GetCustomAttribute<ExcelColumnAttribute>();
                    var cell = headerRow.CreateCell(i);
                    cell.SetCellValue(attr?.Name ?? prop.Name);
                    cell.CellStyle = headerStyle;
                }

                // 创建数据样式
                var dataStyle = workbook.CreateCellStyle();
                dataStyle.BorderBottom = BorderStyle.Thin;
                dataStyle.BorderTop = BorderStyle.Thin;
                dataStyle.BorderLeft = BorderStyle.Thin;
                dataStyle.BorderRight = BorderStyle.Thin;
                // 填充数据
                var dataList = data.ToList();
                for (int row = 0; row < dataList.Count; row++)
                {
                    var dataRow = sheet.CreateRow(row + 1);
                    var item = dataList[row];

                    for (int col = 0; col < properties.Count; col++)
                    {
                        var prop = properties[col];
                        var value = prop.GetValue(item);
                        var cell = dataRow.CreateCell(col);

                        SetCellValue(cell, value, prop);
                        cell.CellStyle = dataStyle;

                        var attr = prop.GetCustomAttribute<ExcelColumnAttribute>();
                        if (!string.IsNullOrEmpty(attr?.Format))
                        {
                            var cellStyle = workbook.CreateCellStyle();
                            cellStyle.CloneStyleFrom(dataStyle);
                            var dataFormat = workbook.CreateDataFormat();
                            cellStyle.DataFormat = dataFormat.GetFormat(attr.Format);
                            cell.CellStyle = cellStyle;
                        }
                    }
                }

                // 自动调整列宽
                for (int i = 0; i < properties.Count; i++)
                {
                    var width = properties[i].GetCustomAttribute<ExcelColumnAttribute>()?.Width ?? 0;
                    if (width==0)
                    {
                        sheet.AutoSizeColumn(i);
                    }
                    else
                    {
                        sheet.SetColumnWidth(i, width*25);
                    }
                    // 设置最大宽度，避免过宽
                    if (sheet.GetColumnWidth(i) > 15000)
                    {
                        sheet.SetColumnWidth(i, 15000);
                    }
                }

                using var ms = new MemoryStream();
                workbook.Write(ms);
                workbook.Close();

                return await Task.FromResult(ms.ToArray());
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出Excel时发生错误");
                throw;
            }
        }
        [DisableValidation]
        public virtual async Task<List<T>> ImportFromExcelAsync(Stream fileStream)
        {
            var result = await ImportFromExcelWithValidationAsync(fileStream);
            return result.SuccessData;
        }
        [DisableValidation]
        public virtual async Task<ImportResult<T>> ImportFromExcelWithValidationAsync(Stream fileStream)
        {
            var result = new ImportResult<T>();

            try
            {
                var workbook = GetWorkbook(fileStream);
                var sheet = workbook.GetSheetAt(0);

                if (sheet.LastRowNum < 1)
                {
                    return result;
                }

                var properties = GetImportProperties();
                var columnMapping = GetColumnMapping(sheet, properties);

                result.TotalRows = sheet.LastRowNum; // 排除表头

                for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                 
                    var row = sheet.GetRow(rowIndex);
                    if (row == null) continue;

                    try
                    {
                        var item = new T();
                        var hasError = false;

                        foreach (var prop in properties)
                        {
                            var attr = prop.GetCustomAttribute<ExcelColumnAttribute>();
                            var columnName = attr?.Name ?? prop.Name;

                            if (!columnMapping.ContainsKey(columnName))
                            {
                                if (attr?.Required == true)
                                {
                                    result.Errors.Add(new ImportError
                                    {
                                        Row = rowIndex + 1,
                                        Column = columnName,
                                        Message = $"必填列 '{columnName}' 未找到"
                                    });
                                    hasError = true;
                                }
                                continue;
                            }

                            var colIndex = columnMapping[columnName];
                            var cell = row.GetCell(colIndex);
                            var cellValue = GetCellValue(cell);

                            try
                            {
                                var convertedValue = ConvertValue(cellValue, prop.PropertyType);
                                prop.SetValue(item, convertedValue);

                                // 验证属性
                                var validationError = ValidateProperty(item, prop, convertedValue, rowIndex + 1, columnName);
                                if (validationError != null)
                                {
                                    result.Errors.Add(validationError);
                                    hasError = true;
                                }
                            }
                            catch (Exception ex)
                            {
                                result.Errors.Add(new ImportError
                                {
                                    Row = rowIndex + 1,
                                    Column = columnName,
                                    Message = $"数据转换失败: {ex.Message}",
                                    Value = cellValue?.ToString()
                                });
                                hasError = true;
                            }
                        }

                        if (!hasError)
                        {
                            // 执行整体对象验证
                            var objectValidationErrors = ValidateObject(item, rowIndex + 1);
                            if (objectValidationErrors.Any())
                            {
                                result.Errors.AddRange(objectValidationErrors);
                            }
                            else
                            {
                                result.SuccessData.Add(item);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        result.Errors.Add(new ImportError
                        {
                            Row = rowIndex + 1,
                            Message = $"处理行数据时发生错误: {ex.Message}"
                        });
                    }
                }

                workbook.Close();
                return await Task.FromResult(result);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导入Excel时发生错误");
                result.Errors.Add(new ImportError
                {
                    Message = $"文件读取失败: {ex.Message}"
                });
                return result;
            }
        }

        protected virtual IWorkbook GetWorkbook(Stream fileStream)
        {
            try
            {
                // 首先尝试作为XLSX文件打开
                return new XSSFWorkbook(fileStream);
            }
            catch
            {
                try
                {
                    // 如果失败，尝试作为XLS文件打开
                    fileStream.Seek(0, SeekOrigin.Begin);
                    return new HSSFWorkbook(fileStream);
                }
                catch (Exception ex)
                {
                    throw new InvalidOperationException("不支持的文件格式，请使用Excel文件(.xlsx或.xls)", ex);
                }
            }
        }

        protected virtual void SetCellValue(ICell cell, object value, PropertyInfo property)
        {
            if (value == null)
            {
                cell.SetCellValue("");
                return;
            }

            var type = property.PropertyType;
            var underlyingType = Nullable.GetUnderlyingType(type) ?? type;

            switch (Type.GetTypeCode(underlyingType))
            {
                case TypeCode.String:
                    cell.SetCellValue(value.ToString());
                    break;
                case TypeCode.Int32:
                case TypeCode.Int64:
                case TypeCode.Int16:
                    cell.SetCellValue(Convert.ToDouble(value));
                    break;
                case TypeCode.Double:
                case TypeCode.Single:
                case TypeCode.Decimal:
                    cell.SetCellValue(Convert.ToDouble(value));
                    break;
                case TypeCode.DateTime:
                    cell.SetCellValue((DateTime)value);
                    break;
                case TypeCode.Boolean:
                    cell.SetCellValue((bool)value);
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }

        protected virtual object GetCellValue(ICell cell)
        {
            if (cell == null)
                return null;

            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                        return cell.DateCellValue;
                    return cell.NumericCellValue;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Formula:
                    return GetCellValue(cell); // 递归处理公式
                case CellType.Blank:
                    return null;
                default:
                    return cell.ToString();
            }
        }

        protected virtual List<PropertyInfo> GetExportProperties()
        {
            return typeof(T).GetProperties()
                .Where(p => !p.GetCustomAttributes<ExcelColumnAttribute>().Any(a => a.Ignore))
                .OrderBy(p => p.GetCustomAttribute<ExcelColumnAttribute>()?.Order ?? 999)
                .ToList();
        }

        protected virtual List<PropertyInfo> GetImportProperties()
        {
            return typeof(T).GetProperties()
                .Where(p => p.CanWrite && !p.GetCustomAttributes<ExcelColumnAttribute>().Any(a => a.Ignore))
                .ToList();
        }

        protected virtual Dictionary<string, int> GetColumnMapping(ISheet sheet, List<PropertyInfo> properties)
        {
            var mapping = new Dictionary<string, int>();
            var headerRow = sheet.GetRow(0);

            if (headerRow != null)
            {
                for (int col = 0; col < headerRow.LastCellNum; col++)
                {
                    var cell = headerRow.GetCell(col);
                    var headerValue = GetCellValue(cell)?.ToString()?.Trim();
                    if (!string.IsNullOrEmpty(headerValue))
                    {
                        mapping[headerValue] = col;
                    }
                }
            }

            return mapping;
        }

        protected virtual object ConvertValue(object value, Type targetType)
        {
            if (value == null || value == DBNull.Value)
            {
                if (targetType.IsValueType && Nullable.GetUnderlyingType(targetType) == null)
                {
                    return Activator.CreateInstance(targetType);
                }
                return null;
            }

            var underlyingType = Nullable.GetUnderlyingType(targetType) ?? targetType;

            if (underlyingType == typeof(string))
            {
                return value.ToString().Trim();
            }

            if (underlyingType.IsEnum)
            {
                return Enum.Parse(underlyingType, value.ToString(), true);
            }

            if (underlyingType == typeof(DateTime))
            {
                if (value is DateTime dt)
                    return dt;
                if (DateTime.TryParse(value.ToString(), out var dateTime))
                    return dateTime;
                throw new FormatException($"无法将 '{value}' 转换为日期时间格式");
            }

            return Convert.ChangeType(value, underlyingType);
        }

        protected virtual ImportError ValidateProperty(T item, PropertyInfo property, object value, int row, string columnName)
        {
            var validationAttributes = property.GetCustomAttributes<ValidationAttribute>();

            foreach (var attr in validationAttributes)
            {
                if (!attr.IsValid(value))
                {
                    return new ImportError
                    {
                        Row = row,
                        Column = columnName,
                        Message = attr.ErrorMessage ?? $"验证失败",
                        Value = value?.ToString()
                    };
                }
            }

            var excelAttr = property.GetCustomAttribute<ExcelColumnAttribute>();
            if (excelAttr?.Required == true && (value == null || string.IsNullOrWhiteSpace(value.ToString())))
            {
                return new ImportError
                {
                    Row = row,
                    Column = columnName,
                    Message = "该字段为必填项",
                    Value = value?.ToString()
                };
            }

            return null;
        }

        protected virtual List<ImportError> ValidateObject(T item, int row)
        {
            var errors = new List<ImportError>();
            var validationContext = new ValidationContext(item);
            var validationResults = new List<ValidationResult>();

            if (!Validator.TryValidateObject(item, validationContext, validationResults, true))
            {
                errors.AddRange(validationResults.Select(vr => new ImportError
                {
                    Row = row,
                    Message = vr.ErrorMessage,
                    Column = string.Join(",", vr.MemberNames)
                }));
            }

            return errors;
        }
    }
}
